package com.source.labelAndBarcode.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.source.labelAndBarcode.entity.BarcodeGrant;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.source.labelAndBarcode.vo.BarcodeGrantVo;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Joey
 * @since 2021-02-23
 */
public interface BarcodeGrantMapper extends BaseMapper<BarcodeGrant> {

    @Select(value = "SELECT\n" +
            "\tbg.*,\n" +
            "\tstallsName,\n" +
            "\tsu.`name` receiveName, \n" +
            "\tss.`name` deliverName\n" +
            "\tFROM\n" +
            "\tstalls,\n" +
            "\tbarcode_grant bg,\n" +
            "\tsys_user su,\n" +
            "\tsys_user ss\n" +
            "WHERE\n" +
            "\tbg.stallsId = stalls.stallsId \n" +
            "\tAND su.userId = bg.receivePerson \n" +
            "\tAND ss.userId = bg.grantPerson AND bg.isDelete = 0 \n" +
            "\t")
    List<BarcodeGrantVo> findBarcodeGrantAll();

    @Select(value = "SELECT\n" +
            "\tbg.*,\n" +
            "\tstallsName,\n" +
            "\tsu.`name` receiveName, \n" +
            "\tss.`name` deliverName\n" +
            "\tFROM\n" +
            "\tstalls,\n" +
            "\tbarcode_grant bg,\n" +
            "\tsys_user su,\n" +
            "\tsys_user ss\n" +
            "WHERE\n" +
            "\tbg.stallsId = stalls.stallsId \n" +
            "\tAND su.userId = bg.receivePerson \n" +
            "\tAND ss.userId = bg.grantPerson AND bg.isDelete = 0 \n" +
            "\t")
    IPage<BarcodeGrantVo> findBarcodeGrantAllOnPagination(Page<BarcodeGrantVo> page);

    @Select(value = "SELECT\n" +
            "\tbg.*,\n" +
            "\tstallsName,\n" +
            "\tsu.`name` receiveName, \n" +
            "\tss.`name` deliverName\n" +
            "\tFROM\n" +
            "\tstalls,\n" +
            "\tbarcode_grant bg,\n" +
            "\tsys_user su,\n" +
            "\tsys_user ss\n" +
            "WHERE\n" +
            "\tbg.stallsId = stalls.stallsId \n" +
            "\tAND su.userId = bg.receivePerson \n" +
            "\tAND ss.userId = bg.grantPerson AND bg.barcodeGrantId = #{id} AND bg.isDelete = 0 \n" +
            "\t")
    BarcodeGrantVo findById(String id);

    @Select(value = "select sum(grantCount) from barcode_grant where grantDate between #{fromTime} and #{toTime} AND isDelete = 0")
    int getGrantCountByTime(Date fromTime, Date toTime);

    @Select(value = "SELECT\n" +
            "\tbg.*, stallsName, su.`name` receiveName, ss.`name` deliverName \n" +
            "FROM\n" +
            "\tstalls, barcode_grant bg, sys_user su, sys_user ss \n" +
            "WHERE\n" +
            "\tbg.stallsId = stalls.stallsId \n" +
            "\tAND su.userId = bg.receivePerson \n" +
            "\tAND ss.userId = bg.grantPerson \n" +
            "\tAND CONCAT(stallsName,grantDate,grantCount,startNumber,endNumber,su.`name`,ss.`name`) LIKE #{keyWord} " +
            "AND bg.isDelete = 0 ")
    IPage<BarcodeGrantVo> findByKeyWord(Page<BarcodeGrantVo> page, String keyWord);
}
